/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.SeminarEntities;
import org.apt.connectDB.MyConnect;

/**
 *
 * @author Administrator
 */
public class SeminarDao {

    /**
     * function  Get All Seminar
     * @return list seminar
     */
    public static List<SeminarEntities> getAllSeminar() {
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * FROM Seminar";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            if (rs.next()) {
                while (rs.next()) {
                    SeminarEntities seminar = new SeminarEntities();
                    seminar.setSeminarID(rs.getInt("SeminarID"));
                    seminar.setSeminarName(rs.getString("SeminarName"));
                    seminar.setImage(rs.getString("Image"));
                    seminar.setStart(rs.getString("Start"));
                    seminar.setEnd(rs.getString("End"));
                    seminar.setSeminarDay(rs.getString("SeminarDay"));
                    seminar.setLocation(rs.getString("Location"));
                    seminar.setContent(rs.getString("Content"));
                    seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                    seminar.setMaxJoin(rs.getInt("MaxJoin"));
                    seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                    seminar.setStatus(rs.getBoolean("Status"));
                    seminar.setDescription(rs.getString("Description"));
                    seminarlist.add(seminar);
                }
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    /**
     * function  Get 10 record per onne caccess
     * @param pageno : number of page )
     * @return list seminar
     */
    public static List<SeminarEntities> getSeminar(int pageno) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        int pageno2;
        try {
            if (pageno <= 1) {
                pageno = 1;
            } else if (pageno == 2) {
                pageno = 10 + 1;
            } else if (pageno > 2) {
                pageno = (pageno - 1) * 10 + 1;
            }
            pageno2 = pageno + 9;
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY SeminarID) AS row,* FROM Seminar)  as tblTemp WHERE  row between " + pageno + " AND " + pageno2 + " ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                SeminarEntities seminar = new SeminarEntities();
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
                seminarlist.add(seminar);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    /**
     * Function Search Seminar
     * @param pageno, seminar name of new
     * @return List Seminar
     */
    public static List<SeminarEntities> SearchSeminarName(String seminarName) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT top 10 * FROM  Seminar WHERE SeminarName LIKE N'" + seminarName + "%'";
            rs = st.executeQuery(sql);
            System.out.println(sql);
            while (rs.next()) {
                SeminarEntities seminar = new SeminarEntities();
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
                seminarlist.add(seminar);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SeminarDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    /**
     * functiion get One Seminar
     * @param seminarID
     * @return one Seminar
     */
    public static SeminarEntities getSeminarByID(int seminarID) {
        SeminarEntities seminar = new SeminarEntities();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * FROM Seminar where SeminarID=" + seminarID + "";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminar;
    }

    /**
     * function check  Current Join of Seminar
     * @param seminarID
     * @return
     */
//    public static boolean CheckCurrentJoinRegisterSeminar(int seminarID) {
//        boolean resuilt = false;
//        try {
//            SeminarEntities seminar = getSeminarByID(seminarID);
//            String datecompare = seminar.getSeminarDay();
//            String datenow = new java.sql.Date(System.currentTimeMillis()).toString();
//            String[] a = datenow.split("-");
//            String[] b = datecompare.split("-");
//            if (Integer.parseInt(a[0]) <= Integer.parseInt(b[0]) && Integer.parseInt(a[1]) <= Integer.parseInt(b[1]) && Integer.parseInt(a[2]) <= Integer.parseInt(b[2])) {
//                resuilt = true;
//                if (seminar.getCurrentJoin() < seminar.getMaxJoin()) {
//                    resuilt = true;
//                } else {
//                    resuilt = false;
//                }
//            } else {
//                resuilt = false;
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return resuilt;
//    }
    public static boolean CheckCurrentJoinRegisterSeminar(int seminarID) {
        boolean resuilt = false;
        try {
            SeminarEntities seminar = getSeminarByID(seminarID);
            String datecompare = seminar.getSeminarDay();
            String datenow = new java.sql.Date(System.currentTimeMillis()).toString();
            String[] dnow = datenow.split("-");
            String[] dEvent = datecompare.split("-");
            if (Integer.parseInt(dnow[0]) <= Integer.parseInt(dEvent[2])) {
                if (Integer.parseInt(dnow[0]) == Integer.parseInt(dEvent[2])) {
                    resuilt = true;
                    if (Integer.parseInt(dnow[1]) <= Integer.parseInt(dEvent[1])) {
                        resuilt = true;
                        if (Integer.parseInt(dnow[2]) <= Integer.parseInt(dEvent[0])) {
                            resuilt = true;
                            if (seminar.getCurrentJoin() < seminar.getMaxJoin()) {
                                resuilt = true;
                            } else {
                                resuilt = false;
                            }
                        } else {
                            resuilt = false;
                        }
                    } else {
                        resuilt = false;
                    }
                } else {
                    if (seminar.getCurrentJoin() < seminar.getMaxJoin()) {
                        resuilt = true;
                    } else {
                        resuilt = false;
                    }
                }
            } else {
                resuilt = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resuilt;
    }

    /**
     * function get All Seminar by SpeakerID
     * @param speakerID
     * @return List Seminar
     */
    public static List<SeminarEntities> GetSeminarNameBySpeakerID(String speakerID) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            //String sql = "SELECT top 10 * FROM  Seminar WHERE SeminarName LIKE N'" + seminarName + "%'";
            String sql = "SELECT top(10) Seminar.*, SpeakerJoin.SpeakerID FROM SpeakerJoin INNER JOIN Seminar ON SpeakerJoin.SeminarID = Seminar.SeminarID WHERE SpeakerJoin.SpeakerID = " + speakerID + "";
            rs = st.executeQuery(sql);
            System.out.println(sql);
            while (rs.next()) {
                SeminarEntities seminar = new SeminarEntities();
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
                seminarlist.add(seminar);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SeminarDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    /**
     * function get list Seminar Registerd by user
     * @param username
     * @param status
     * @return all seminar has been register by user
     */
    public static List<SeminarEntities> GetSeminarMemberRegister(String username, String status) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT Seminar.* FROM PaymentSeminar INNER JOIN Seminar "
                    + "ON PaymentSeminar.SeminarID = Seminar.SeminarID where PaymentSeminar.[User] = '" + username + "'"
                    + " and PaymentSeminar.[StatusPayment]='" + status + "'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                SeminarEntities seminar = new SeminarEntities();
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
                seminarlist.add(seminar);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SeminarDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    /**
     * function get top 5 Seminar
     * @param seminarName
     * @return return top 5 Seminar
     */
    public static List<SeminarEntities> GetSeminarTop5() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<SeminarEntities> seminarlist = new ArrayList<SeminarEntities>();
        try {
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT Top(5) * FROM Seminar order by NEWID()";
            rs = st.executeQuery(sql);
            System.out.println(sql);
            while (rs.next()) {
                SeminarEntities seminar = new SeminarEntities();
                seminar.setSeminarID(rs.getInt("SeminarID"));
                seminar.setSeminarName(rs.getString("SeminarName"));
                seminar.setImage(rs.getString("Image"));
                seminar.setStart(rs.getString("Start"));
                seminar.setEnd(rs.getString("End"));
                seminar.setSeminarDay(rs.getString("SeminarDay"));
                seminar.setLocation(rs.getString("Location"));
                seminar.setContent(rs.getString("Content"));
                seminar.setCurrentJoin(rs.getInt("CurrentJoin"));
                seminar.setMaxJoin(rs.getInt("MaxJoin"));
                seminar.setPriceJoin(rs.getFloat("PriceJoin"));
                seminar.setStatus(rs.getBoolean("Status"));
                seminar.setDescription(rs.getString("Description"));
                seminarlist.add(seminar);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SeminarDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return seminarlist;
    }

    public static void main(String[] args) {
        boolean a = CheckCurrentJoinRegisterSeminar(1);
        System.out.println(a);
    }
}
